From: <Saved by Windows Internet Explorer 7>
Subject: HowtoConnectToMicrosoftSQLServer in Ruby on Rails
Date: Fri, 9 May 2008 14:40:13 +0200
MIME-Version: 1.0
Content-Type: multipart/related;
	type="text/html";
	boundary="----=_NextPart_000_0000_01C8B1E2.971685A0"
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3198

This is a multi-part message in MIME format.

------=_NextPart_000_0000_01C8B1E2.971685A0
Content-Type: text/html;
	charset="utf-8"
Content-Transfer-Encoding: quoted-printable
Content-Location: http://wiki.rubyonrails.com/rails/pages/HowtoConnectToMicrosoftSQLServer

=EF=BB=BF<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" =
"http://www.w3c.org/TR/1999/REC-html401-19991224/loose.dtd">
<HTML =
xmlns=3D"http://www.w3.org/1999/xhtml"><HEAD><TITLE>HowtoConnectToMicroso=
ftSQLServer in Ruby on Rails</TITLE>
<META http-equiv=3DContent-Type content=3D"text/html; =
charset=3DUTF-8"><LINK title=3DRSS=20
href=3D"http://wiki.rubyonrails.org/rails/feed.rss" =
type=3Dapplication/rss+xml=20
rel=3Dalternate>
<STYLE type=3Dtext/css>H1#pageName {
	COLOR: #393
}
.newWikiWord A {
	COLOR: #393
}
A.existingWikiWord {
	COLOR: #393
}
.newWikiWord A:hover {
	COLOR: #393
}
#TextileHelp H3 {
	COLOR: #393
}
#Container {
	WIDTH: 600px
}
#Content {
	WIDTH: 600px
}
</STYLE>
<LINK media=3Dscreen=20
href=3D"http://wiki.rubyonrails.com/stylesheets/instiki.css?1171099937"=20
type=3Dtext/css rel=3DStylesheet><LINK media=3Dscreen=20
href=3D"http://wiki.rubyonrails.com/stylesheets/captcha.css?1171099937"=20
type=3Dtext/css rel=3DStylesheet>
<STYLE type=3Dtext/css></STYLE>

<META content=3D"MSHTML 6.00.6000.16640" name=3DGENERATOR></HEAD>
<BODY>
<DIV id=3DContainer>
<DIV id=3DHeader>
<DIV id=3DNav><IMG height=3D16 alt=3DFavicon=20
src=3D"http://wiki.rubyonrails.com/images/favicon.png?1171099937" =
width=3D16> <A=20
href=3D"http://www.rubyonrails.org/">Ruby on Rails</A> | <A=20
href=3D"http://www.rubyonrails.org/screencasts">Screencasts</A> | <A=20
href=3D"http://www.rubyonrails.org/down">Download</A> | <A=20
href=3D"http://www.rubyonrails.org/docs">Documentation</A> | <A=20
href=3D"http://weblog.rubyonrails.org/">Weblog</A> | <A class=3Dcurrent=20
href=3D"http://www.rubyonrails.org/community">Community</A> | <A=20
href=3D"http://dev.rubyonrails.org/">Source</A> </DIV></DIV>
<DIV id=3DContent>
<DIV id=3Di2Nav>
<H1 id=3DpageName><SMALL>Ruby on=20
Rails</SMALL><BR>HowtoConnectToMicrosoftSQLServer</H1>
<FORM class=3Dnavigation id=3DnavigationForm><A=20
href=3D"http://wiki.rubyonrails.org/rails">Home Page</A> | <A=20
href=3D"http://wiki.rubyonrails.org/rails/pages">All Pages</A> | <A=20
href=3D"http://wiki.rubyonrails.org/rails/recent">Recently Revised</A> | =
<A=20
href=3D"http://wiki.rubyonrails.org/rails/feed.rss">Feed</A> =
</FORM></DIV>
<P>These instructions pertain to connecting from a Windows computer. If =
you need=20
to connect from a non-Windows computer, follow these instructions: <A=20
class=3DexistingWikiWord=20
href=3D"http://wiki.rubyonrails.org/rails/pages/HowtoConnectToMicrosoftSQ=
LServerFromRailsOnLinux">HowtoConnectToMicrosoftSQLServerFromRailsOnLinux=
</A>.</P>
<P>For <SPAN class=3Dcaps>OSX</SPAN> using iODBC see <A =
class=3DexistingWikiWord=20
href=3D"http://wiki.rubyonrails.org/rails/pages/HowtoConnectToMicrosoftSQ=
LServerFromRailsOnOSX">HowtoConnectToMicrosoftSQLServerFromRailsOnOSX</A>=
.</P>For=20
FreeBSD see <A class=3DexistingWikiWord=20
href=3D"http://wiki.rubyonrails.org/rails/pages/HowtoConnectToMicrosoftSQ=
LServerFromRailsOnFreeBSD">HowtoConnectToMicrosoftSQLServerFromRailsOnFre=
eBSD</A>.<BR>
<HR>

<P><STRONG>Starting with Rails 2.0</STRONG> the ActiveRecord sqlserver =
adapter=20
is no longer included by default and must be installed =
separately:</P><PRE> gem install activerecord-sqlserver-adapter =
--source=3Dhttp://gems.rubyonrails.org </PRE>
<HR>
<BR>Rails 1.X (Rails 2.X needs to do this, too) <BR>Get the latest =
source=20
distribution of Ruby-DBI from: <A=20
href=3D"http://rubyforge.org/projects/ruby-dbi/">http://rubyforge.org/pro=
jects/ruby-dbi/</A>=20
and copy the file: <PRE>lib/dbd/ADO.rb</PRE>
<P>to:</P><PRE>X:/ruby/lib/ruby/site_ruby/1.8/DBD/ADO/ADO.rb</PRE>
<P>Then in your database.yml</P>
<P>For Rails 2.X:</P><PRE><CODE>development:
  adapter: sqlserver
  database: database_name
  host: server_name
  username: user_name
  password: your_pw_here</CODE></PRE>
<P>For Rails 1.X:</P><PRE><CODE>development:
  adapter: sqlserver
  database: database_name
  host: DBI:ADO:Provider=3DSQLOLEDB;Data Source=3Dserver_name;Initial =
Catalog=3Ddatabase_name;User Id=3Duser_name;Password=3Dyour_pw_here;
  username: user_name
  password: your_pw_here</CODE></PRE>
<P>And that should be it!</P>
<P>Note that setting the username, password, or database name in the =
=E2=80=98host:=E2=80=99=20
string does <SPAN class=3Dcaps>NOT</SPAN> work in 2.X, because the =
database=20
adaptor overrides the host: string with the values given for database:,=20
username:, and password:</P>
<P>If the database server is running on the same machine as Rails, you =
can use=20
=E2=80=99.=E2=80=99 as the host name. If you have more than one <SPAN =
class=3Dcaps>SQL=20
</SPAN>Server running, or your <SPAN class=3Dcaps>SQL </SPAN>Server is =
named, the=20
server_name may look like =E2=80=9CLYNX\MSSMLBIZ=E2=80=9D if your =
computer name is =E2=80=9CLYNX=E2=80=9D and=20
your <SPAN class=3Dcaps>SQL </SPAN>Server instance is named =
=E2=80=9CMSSMSBIZ=E2=80=9D, or=20
=E2=80=9D.\MSSMLBIZ=E2=80=9D on the localhost.</P>
<P>Note: This may be obvious but remember to avoid tabs in your yml, =
especially=20
if you copy and paste this code.</P>
<P><EM>Note: Use this if your <SPAN class=3Dcaps>SQL </SPAN>Server is on =
a=20
non-standard port:</EM></P><PRE><CODE>development:
  adapter: sqlserver
  database: database_name
  host: server_name,5555
  username: user_name
  password: your_pw_here</CODE></PRE>
<P><STRONG>Got <SPAN class=3Dcaps>OLE</SPAN> error code =
80004005</STRONG><BR>I=E2=80=99ve=20
tried to follow exactly the previous steps with NO success, what I got =
is the=20
following error:<BR>Open <SPAN class=3Dcaps>OLE</SPAN> error =
code:80004005 in=20
Microsoft <SPAN class=3Dcaps>OLE DB </SPAN>Provider for <SPAN =
class=3Dcaps>SQL=20
</SPAN>Server (Connect()).]SQL Server does not exist or access denied. =
<SPAN=20
class=3Dcaps>HRESULT</SPAN> error code:0=C3=9780020009 Exception =
occurred.</P>
<P>Any idea on this? <EM><STRONG>.......After several trials I got the =
next=20
solution:</STRONG></EM></P>
<UL>
  <LI>I got and copy the last Ruby-DBI gem and <SPAN =
class=3Dcaps>ADO</SPAN>.rb=20
  file as indicated in the beginning of this article.=20
  <LI>In <SPAN class=3Dcaps>MY CASE I</SPAN> did <SPAN =
class=3Dcaps>NOT</SPAN> use=20
  the parameter<BR><EM>=E2=80=9Dhost: <SPAN=20
  class=3Dcaps>DBI</SPAN>:ADO:Provider=3DSQLOLEDB;Data =
Source=3Dserver_name;Initial=20
  Catalog=3Ddatabase_name;User =
Id=3Duser_name;Password=3Dyour_pw_here;=E2=80=9D</EM> that=20
  appears in the databe.yml file &lt;=3D Every time I use it I got the =
same <SPAN=20
  class=3Dcaps>OLE</SPAN> error code.=20
  <LI>Instead I used a little changed format in my database.yml file, =
here is=20
  mine for reference:<BR><PRE><CODE>
development:
  adapter: sqlserver
  database: demoPba0_development
  host: LAP-2F89FF92BC
  username: sa
  password: password1

test:
  adapter: sqlserver
  database: demoPba0_test
  host: LAP-2F89FF92BC
  username: sa
  password: password1

production:
  adapter: sqlserver
  database: demoPba0_production
  host: LAP-2F89FF92BC
  username: sa
  password: password1
</CODE></PRE><BR>Where:<BR>-LAP-2F89FF92BC is the name of my machine.=20
</LI></UL>Also is important to ensure to include a colon =
=E2=80=99:=E2=80=99and a space =E2=80=99 =E2=80=99=20
after the name of each parameter, for instance:=20
<UL>
  <LI>=E2=80=9Cusername: sa=E2=80=9D is right=20
  <LI>=E2=80=9Cusername:sa=E2=80=9D is not recommended </LI></UL>
<P>Best Regards !!!</P>
<H2>Configuring <SPAN class=3Dcaps>SQL </SPAN>Server for <SPAN =
class=3Dcaps>SQL=20
</SPAN>Server Authentication</H2>
<P>There is lots of info on <SPAN class=3Dcaps>MSDN</SPAN>, but =
don=E2=80=99t forget to=20
enable <SPAN class=3Dcaps>SQL </SPAN>Server Authentication (SQL Server =
only allows=20
Windows Authentication mode by default). Use <SPAN class=3Dcaps>SQL =
</SPAN>Server=20
Management Studio (Express), connect to the server, right-click on the =
server,=20
choose Properties, choose the Security page, and choose <SPAN =
class=3Dcaps>SQL=20
</SPAN>Server and Windows Authentication mode.</P>
<P>(You can check the <SPAN class=3Dcaps>SQL </SPAN>Server log after a =
failed=20
login attempt (depending on those Security Properties) to see what the =
problem=20
is.)</P>
<P>Then, in the treeview, select Security/Logins, right-click the list =
view,=20
choose New Login=E2=80=A6, and set it up for <SPAN class=3Dcaps>SQL =
</SPAN>Server=20
Authentication.</P>
<P>Then, once you have set up your login, you need to create your =
databases and=20
enable your new login to access them. From the treeview, click on =
Databases, and=20
in the list view, right-click, choose New Database=E2=80=A6, and give it =
a name (like=20
mydb_development, mydb_test, and mydb_production). Then, in the =
treeview, click=20
on each database, select Security/Users, and in the list view, click =
=E2=80=9CNew=20
User=E2=80=A6=E2=80=9D. Enter both a =E2=80=9CUser name=E2=80=9D (which =
is arbitrary) and a login name (which=20
matches the login name you created at the previous step). Add =
=E2=80=9CRole Memberships=E2=80=9D=20
of db_datareader, db_datawriter, and db_ddladmin. Click OK and you are =
ready to=20
rake!</P>
<P>Question: Is there away to set a timeout? If I query a locked row my =
program=20
hangs.</P>
<H2>Making Rake=E2=80=99s database tasks work (<A =
class=3DexistingWikiWord=20
href=3D"http://wiki.rubyonrails.org/rails/pages/yawn">yawn</A>)</H2>
<P>Append <CODE>'&lt;Drive&gt;\&lt;ProgramFolder&gt;\Microsoft SQL=20
Server\MSSQL\Upgrade'</CODE> to your <SPAN class=3Dcaps>PATH</SPAN> =
environment=20
variable =E2=80=93 this makes scptxfr.exe et. al. available for =
Rake.</P>
<P><EM>I didn=E2=80=99t need to do this in <SPAN class=3Dcaps>SQL =
</SPAN>Server 2005 with=20
Rails 1.1.4, which is fortunate because this file doesn=E2=80=99t exist =
in <SPAN=20
class=3Dcaps>SQL </SPAN>Server 2005, as was noted in the below =
comments</EM></P>
<H2>Lowercasing it all (by <A class=3DexistingWikiWord=20
href=3D"http://wiki.rubyonrails.org/rails/pages/RonLusk">RonLusk</A>)</H2=
>
<P>Because our <SPAN class=3Dcaps>SQL </SPAN>Servers are full of =
mixed-case field=20
names, I dinked with <CODE>sqlserver_adapter.rb</CODE> <EM>(found at =
=E2=80=93=20
x:\Ruby\lib\ruby\gems\1.8\gems\activerecord-1.10.1\lib\active_record\conn=
ection_adapters\sqlserver_adapter.rb)</EM>=20
to lowercase all the column names. In the <CODE>columns(table_name,=20
name=3Dnil)</CODE> method, I turned <BR></P><PRE><CODE>
  SELECT COLUMN_NAME as...
</CODE></PRE><BR>into<BR><PRE><CODE>
  SELECT LOWER(COLUMN_NAME) as...
</CODE></PRE><BR>(around line 178), and in the private method =
<CODE>select(sql,=20
name, @connection)</CODE> has a line<BR><PRE><CODE>
  record[col] =3D row[col]
</CODE></PRE><BR>that became<BR><PRE><CODE>
  record[col.downcase] =3D row[col]
</CODE></PRE><BR>Now all the attributes look lowercase, and I =
don=E2=80=99t get odd=20
=E2=80=9Cthat=E2=80=99s a constant!=E2=80=9D messages from ruby or from =
my eyes.
<P></P>
<P><B>Note:</B> I also had to modify the <CODE>cast_to_time</CODE> =
method to=20
dink with the return time if it was before 1970, but that=E2=80=99s a =
special case for=20
this database, perhaps.</P>
<P>As an alternative to the above you can hack row.rb in the <SPAN=20
class=3Dcaps>DBI</SPAN> directory to map the colum names to uppercase or =
lowercase=20
when it creates the hash it uses internally. There is also a patch on =
the=20
rubyforge site to make the fetching of the rows case insensitive.</P>
<P><B>Note for ActiveRecord 1.15:</B></P>
<P>The <CODE>select</CODE> method has changed, so you have to =
change<BR></P><PRE><CODE>row_hash[handle.column_names[i]] =3D =
value</CODE></PRE><BR>to =
this<BR><PRE><CODE>row_hash[handle.column_names[i].downcase] =3D =
value</CODE></PRE><BR>Also,=20
in the <CODE>columns</CODE> method, you=E2=80=99re looking for =
this:<BR><PRE><CODE>
SELECT
cols.COLUMN_NAME as ColName
</PRE></CODE><BR>and changing it to<BR><PRE><CODE>
SELECT
LOWER(cols.COLUMN_NAME) as ColName
</PRE></CODE>
<P></P>
<P><B>Further fix required when lowercasing column names</B></P>
<P>You need to make the following further fixes to avoid unexpected=20
behaviour:</P>
<P>In the insert method:<BR></P><PRE><CODE>  id_value || =
select_one("SELECT @@IDENTITY AS =
Ident")["Ident"]</PRE></CODE><BR>to:<BR><PRE><CODE>  id_value || =
select_one("SELECT @@IDENTITY AS ident")["ident"]</PRE></CODE>
<P></P>
<P>In the update method:<BR></P><PRE><CODE>  end || select_one("SELECT =
@@ROWCOUNT AS =
AffectedRows")["AffectedRows"]</PRE></CODE><BR>to:<BR><PRE><CODE>  end =
|| select_one("SELECT @@ROWCOUNT AS =
affectedrows")["affectedrows"]</PRE></CODE>
<P></P>
<P>In the add_limit_offset! method:<BR></P><PRE><CODE>  total_rows =3D =
@connection.select_all("SELECT count(*) as TotalRows from =
(#{sql.gsub(/\bSELECT(\s+DISTINCT)?\b/i, "SELECT#{$1} TOP 1000000000")}) =
tally")[0][:TotalRows].to_i</PRE></CODE><BR>to:<BR><PRE><CODE>  =
total_rows =3D @connection.select_all("SELECT count(*) as totalrows from =
(#{sql.gsub(/\bSELECT(\s+DISTINCT)?\b/i, "SELECT#{$1} TOP 1000000000")}) =
tally")[0][:totalrows].to_i</PRE></CODE>
<P></P>
<P>One symptom of the problem is the ActiveRecord::Base#create method =
returning=20
nil ids.</P>
<H2>Avoiding the \NoMethodError problem</H2>
<P>You will have to patch the=20
x:\Ruby\lib\ruby\gems\1.8\gems\activerecord-1.10.1\lib\active_record\conn=
ection_adapters\sqlserver_adapter.rb=20
file or you will always receive a <CODE>\NoMethodError</CODE> on trying =
to do a=20
simple <CODE>SELECT</CODE> on your <SPAN class=3Dcaps>SQL</SPAN> server =
table.</P>
<P>The patch details are here =E2=80=93 <A=20
href=3D"http://dev.rubyonrails.org/ticket/1160">http://dev.rubyonrails.or=
g/ticket/1160</A>=20
=E2=80=93 you will have to cut and paste the code into the =
sqlserver_adapter.rb file=20
above.</P>
<P><EM>This has been fixed in later versions of activerecord</EM>.</P>
<H2>Newbie=E2=80=99s experiences with <SPAN =
class=3Dcaps>SQL</SPAN>Server 2005 Beta 2</H2>
<P>The major fix (apart from the ones suggest above) was to patch=20
<CODE>sqlserver_adapter.rb</CODE> to force it to use of the new provider =
name=20
=E2=80=98SQLNCLI=E2=80=99 instead of =E2=80=98SQLOLEDB=E2=80=99. One =
thing different from above was that I=20
didn=E2=80=99t specify the connection string in database.yml=E2=80=99s =
host line, i only=20
specified the database machine name. I tried with host having the =
connection=20
string and it didn=E2=80=99t seem to work. At least i=E2=80=99m up and =
running now. :)</P>
<P>I used a different approach in the <CODE>database.yml</CODE> added a=20
provider:<BR></P><PRE>development:
  adapter: sqlserver
  database: database
  username: user
  password: pwd
  host: .\SQLEXPRESS
  mode: DBI:ADO
  provider: SQLNCLI</PRE><BR>and in the =
<CODE>sqlserver_adapter.rb</CODE>=20
added<BR><PRE>provider    =3D config[:provider] ? config[:provider].to_s =
: 'SQLOLEDB'
</PRE><BR>and modified the line 39 to:<BR><PRE>driver_url =3D =
"DBI:ADO:Provider=3D#{provider};Data Source=3D#{host};Initial =
Catalog=3D#{database};User =
Id=3D#{username};Password=3D#{password};"</PRE><BR>and=20
it is working fine.
<P></P>
<H2>Another newbie=E2=80=99s experience with <SPAN class=3Dcaps>SQL =
</SPAN>Server 2005=20
final</H2>
<P>The rails 1.0 sql server tasks use a =E2=80=9Cscptxfr.exe=E2=80=9D =
that is not in the 2005=20
distribution. However, if you have a 7.0/2000 install around, I was able =
to =E2=80=9Cup=20
port=E2=80=9D the scptxfr.exe (along with its two minor dependencies =
sqlresld.dll and=20
Resources\1033\scptxfr.rll) to my <SPAN class=3Dcaps>SQL </SPAN>Server =
2005=20
machine (I put them in rails_app/db/tools so as not to muck around in =
Program=20
Files) and now rails 1.0/SQL Server 2005 final is working nicely.</P>
<P>Other than =E2=80=9Cscptxfr.exe=E2=80=9D, I didn=E2=80=99t have to =
make any other patches/fixes/etc.=20
(well, installing <SPAN class=3Dcaps>ADO</SPAN>.rb aside).</P>
<P><SPAN style=3D"COLOR: red">Has anyone been able to confirm this? What =
is meant=20
by <STRONG>=E2=80=9Dup port=E2=80=9D</STRONG>; did you just copy them =
from the 7/2000 folders to=20
your new folder?</SPAN></P>
<P>Based on this <A=20
href=3D"http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=3D168161&am=
p;SiteID=3D1">link</A>=20
it seems that =E2=80=9Cup port=E2=80=9D or =E2=80=9Cback port=E2=80=9D =
are the same thing and just means coping=20
the files from a version 7/2000 to somewhere in your path on the ruby=20
machine.</P>
<H2>How to go about using Trusted Security</H2>
<P>Go into <CODE>sqlserver_adapter.rb</CODE>, and modify:</P><PRE><CODE>
conn =3D DBI.connect("DBI:ADO:Provider=3DSQLOLEDB;Data =
Source=3D#{host};Initial Catalog=3D#{database};User =
Id=3D#{username};Password=3D#{password};")
</CODE></PRE>
<P>take out: <CODE>User =
Id=3D#{username};Password=3D#{password};</CODE><BR>add:=20
<CODE>Integrated Security=3DSSPI;</CODE></P><PRE><CODE>
      conn =3D DBI.connect("DBI:ADO:Provider=3DSQLOLEDB;Data =
Source=3D#{host};Initial Catalog=3D#{database};Integrated =
Security=3DSSPI")
</CODE></PRE>
<P>Of course, you need to make sure that the user account that is =
running=20
webbrick, Apache, <SPAN class=3Dcaps>IIS</SPAN>, etc., has windows-based =
privs=20
into the particular database.</P>
<P>There is a <A =
href=3D"http://dev.rubyonrails.org/ticket/6580">patch</A> in the=20
Rails bug-tracker that enables you to set up your connection in=20
<CODE>database.yml</CODE> like so:</P><PRE>development
  adapter: sqlserver
  database: nomomysql
  host: nomoora
  windows_auth: true
  ...and any other extended OLEDB flags to pass to this driver as well
</PRE>
<P>and have <CODE>sqlserver_adapter.rb</CODE> build the connect string=20
appropriately. To help get this feature into the next version of rails, =
check=20
out <A href=3D"http://dev.rubyonrails.org/ticket/8862">ticket =
8862</A>.</P>
<H2>How to fix db_schema_dump and stored procedures</H2>
<P>If you get an error like:</P><PRE>DBI::DatabaseError: Count
    OLE error code:8000FFFF in Microsoft OLE DB Provider for SQL Server
      ITransaction::Commit or ITransaction::Abort was called, and object =
is in a
 zombie state.
    HRESULT error code:0x80020009
      Exception occurred.: EXEC sp_helpindex MyTable
</PRE>
<P>You need to turn off the <SPAN class=3Dcaps>DBI </SPAN>AutoCommit =
feature. At=20
the end of environment.rb you can =
add:</P><PRE>ActiveRecord::Base.connection.instance_variable_get("@connec=
tion")["AutoCommit"] =3D false
</PRE>
<H2>Question about the above fix</H2>
<P>Thank you for posting this fix, it works!</P>
<P>However, does this setting negatively affect other operations where I =
need to=20
commit? If so, what=E2=80=99s the code that I need to add to my Ruby =
programs?</P>
<H2>Comment about the above fix</H2>
<P>When I tried to implement this fix, my migrations stopped working. =
Was it=20
fully tested?</P>
<H2>Additional comment on the above fix</H2>
<P>I can confirm that migrations stop working once the above line is =
added to=20
environment.rb in Rails 1.1.2. While schema.rb may appear to =
autogenerate as it=20
should, the database itself does not appear to actually execute any =
changes made=20
in the migration. It seems we=E2=80=99re stuck with working migrations =
and a corrupt=20
schema.rb, or a =E2=80=9Ccorrect=E2=80=9D schema.rb with migrations that =
don=E2=80=99t execute on the=20
DB.</P>
<H2>Using the above fix only for db:schema:dump</H2>
<P>You can create a customized rake task from the original =
db:schema:dump and=20
include the above statement into it. Copy the following into mydb.rake =
and place=20
it to appfolder/lib/tasks:</P><PRE>namespace :mydb do
  task :migrate =3D&gt; :environment do
    ActiveRecord::Migrator.migrate("db/migrate/", ENV["VERSION"] ? =
ENV["VERSION"].to_i : nil)
    Rake::Task["mydb:schema:dump"].invoke if =
ActiveRecord::Base.schema_format =3D=3D :ruby
  end

  namespace :schema do
    task :dump =3D&gt; :environment do
      require 'active_record/schema_dumper'
      File.open(ENV['SCHEMA'] || "db/schema.rb", "w") do |file|
        =
ActiveRecord::Base.connection.instance_variable_get("@connection")["AutoC=
ommit"] =3D false
        ActiveRecord::SchemaDumper.dump(ActiveRecord::Base.connection, =
file)
      end
    end
  end
end
</PRE>
<P>Now you can use =E2=80=9Crake mydb:schema:dump=E2=80=9D to have a =
correct schema.rb file. For=20
migration run =E2=80=9Crake mydb:migrate=E2=80=9D.</P>
<H2>How to fix the =E2=80=9COLE error code:800A0E78=E2=80=9D error</H2>
<P>When calling <CODE>rake db:schema:dump</CODE> on a database =
containing tables=20
without any indexes you get this kind of error:</P><PRE># Could not dump =
table "Table" because of following DBI::DatabaseError
#   EOF
    OLE error code:800A0E78 in ADODB.Recordset
      Operation is not allowed when the object is closed.
    HRESULT error code:0x80020009
      Exception occurred.
</PRE>
<P>The solution, as a newsgroup message said:<BR></P><PRE>I have also =
met this error on tables without any index (on relation tables created =
by older Rails version).
So I have created an unique index on its keys and the error disappeared.

Regards,
Karel
</PRE>
<P></P>
<H2>Connect via <SPAN class=3Dcaps>ODBC</SPAN></H2>
<P>Connecting via the <SPAN class=3Dcaps>OLEDB</SPAN> adaptor seems to =
have it=E2=80=99s=20
quirks. Another posibility is to connect via <SPAN class=3Dcaps>ODBC=20
</SPAN>(tested with Rails 1.1.6)</P>
<P>e.g. for a trusted connection<BR></P><PRE><CODE>
development:
  adapter: sqlserver
  mode: odbc
  dsn: Driver=3D{SQL Server};Server=3D&lt;your =
server&gt;;Database=3D&lt;your db&gt;;Trusted_Connection=3Dyes;

</CODE></PRE><BR>(Original message <A=20
href=3D"http://thread.gmane.org/gmane.comp.lang.ruby.rails/58615/focus=3D=
58615">here</A>)
<P></P>
<H2>SQL Server 2005 Express</H2>
<P>I managed to connect to a <SPAN class=3Dcaps>SQL </SPAN>Server 2005 =
express box=20
using <SPAN class=3Dcaps>ODBC</SPAN> and a trusted windows connection. =
Futher more=20
in my particular case i need to exectue some stored procedures which I =
did by=20
having to set up a custom subclass of ActiveRecord::Base as you =
effectivly have=20
to call the whole <SPAN class=3Dcaps>SQL</SPAN> server <SPAN=20
class=3Dcaps>SQL</SPAN>, so I just made a wrapper for that, nice and =
simple =E2=80=93 hope=20
this helps somone.</P>
<P>I am using the example from the database.yaml at the top of this page =
and am=20
getting the following error when I try to start the WEBrick server =
(using Rails=20
1.2.2):</P>
<P>=3D&gt; Booting=20
WEBrick=E2=80=A6<BR>C:/development/ruby/lib/ruby/gems/1.8/gems/activereco=
rd-1.15.2/lib/active_record<BR>/connection_adapters/abstract/connection_s=
pecification.rb:34:=20
uninitialized clas<BR>s variable @@allow_concurrency in =
ActiveRecord::Base=20
(NameError) from=20
C:/development/ruby/lib/ruby/site_ruby/1.8/rubygems/custom_require.<BR>rb=
:27:in=20
`gem_original_require=E2=80=99 from=20
C:/development/ruby/lib/ruby/site_ruby/1.8/rubygems/custom_require.<BR>rb=
:27:in=20
`require=E2=80=99 from=20
C:/development/ruby/lib/ruby/gems/1.8/gems/activesupport-1.4.1/lib/<BR>ac=
tive_support/dependencies.rb:495:in=20
`require=E2=80=99 from=20
C:/development/ruby/lib/ruby/gems/1.8/gems/activesupport-1.4.1/lib/<BR>ac=
tive_support/dependencies.rb:342:in=20
`new_constants_in=E2=80=99 from=20
C:/development/ruby/lib/ruby/gems/1.8/gems/activesupport-1.4.1/lib/<BR>ac=
tive_support/dependencies.rb:495:in=20
`require=E2=80=99 from=20
C:/development/ruby/lib/ruby/gems/1.8/gems/activerecord-1.15.2/lib/<BR>ac=
tive_record/connection_adapters/abstract_adapter.rb:10=20
from=20
C:/development/ruby/lib/ruby/site_ruby/1.8/rubygems/custom_require.<BR>rb=
:27:in=20
`gem_original_require=E2=80=99 from=20
C:/development/ruby/lib/ruby/site_ruby/1.8/rubygems/custom_require.<BR>rb=
:27:in=20
`require=E2=80=99 ... 27 levels=E2=80=A6 from=20
C:/development/ruby/lib/ruby/gems/1.8/gems/rails-1.2.2/lib/commands<BR>/s=
erver.rb:39=20
from=20
C:/development/ruby/lib/ruby/site_ruby/1.8/rubygems/custom_require.<BR>rb=
:27:in=20
`gem_original_require=E2=80=99 from=20
C:/development/ruby/lib/ruby/site_ruby/1.8/rubygems/custom_require.<BR>rb=
:27:in=20
`require=E2=80=99 from script/server:3</P>
<HR>

<P>I am able to connect to the database from a non-Active Record Ruby=20
application.</P>
<P>I am also getting the same error if I try to use <SPAN=20
class=3Dcaps>ODBC</SPAN>.</P>
<P>Is this a bug in 1.2.x?</P>
<P><STRONG>How to fix =E2=80=9CNot associated with trusted <SPAN =
class=3Dcaps>SQL=20
</SPAN>Server=E2=80=9D error</STRONG></P>
<P>I was unable to get this to work using Windows Authentication in the =
<SPAN=20
class=3Dcaps>SQL </SPAN>Server database. I needed to set my database =
server to=20
=E2=80=9CSQL Server and Windows=E2=80=9D authentication. Then I created =
a user in Enterprise=20
Manager using Tools/Wizards (Database) =E2=80=9CCreate Login =
Wizard.=E2=80=9D I set my=20
database.yml file to use that user.</P>
<P><STRONG>SQL Server 2000 error with Rails 1.2.3</STRONG></P>
<P>I get the same error:<BR>C:\www\demoapp&gt;ruby =
script\server<BR>=3D&gt;=20
Booting=20
WEBrick=E2=80=A6<BR>c:/ruby/lib/ruby/gems/1.8/gems/activerecord-1.15.3/li=
b/active_record/connection_adapters/abstrac<BR>t/connection_specification=
.rb:204:in=20
`establish_connection=E2=80=99: development database is not =
configu<BR>red=20
(ActiveRecord::AdapterNotSpecified) from=20
c:/ruby/lib/ruby/gems/1.8/gems/activerecord-1.15.3/lib/active_record/conn=
ection_ada<BR>pters/abstract/connection_specification.rb:195:in=20
`establish_connection=E2=80=99 from=20
c:/ruby/lib/ruby/gems/1.8/gems/rails-1.2.3/lib/initializer.rb:229:in=20
`initialize_da<BR>tabase=E2=80=99 from=20
c:/ruby/lib/ruby/gems/1.8/gems/rails-1.2.3/lib/initializer.rb:88:in =
`process=E2=80=99=20
from c:/ruby/lib/ruby/gems/1.8/gems/rails-1.2.3/lib/initializer.rb:43:in =
`send=E2=80=99=20
from c:/ruby/lib/ruby/gems/1.8/gems/rails-1.2.3/lib/initializer.rb:43:in =
`run=E2=80=99=20
from ./script/../config/../config/environment.rb:13 from=20
c:/ruby/lib/ruby/site_ruby/1.8/rubygems/custom_require.rb:27:in=20
`gem_original_requi<BR>re=E2=80=99 from=20
c:/ruby/lib/ruby/site_ruby/1.8/rubygems/custom_require.rb:27:in =
`require=E2=80=99 ... 9=20
levels=E2=80=A6 from=20
c:/ruby/lib/ruby/gems/1.8/gems/rails-1.2.3/lib/commands/server.rb:39 =
from=20
c:/ruby/lib/ruby/site_ruby/1.8/rubygems/custom_require.rb:27:in=20
`gem_original_requi<BR>re=E2=80=99 from=20
c:/ruby/lib/ruby/site_ruby/1.8/rubygems/custom_require.rb:27:in =
`require=E2=80=99 from=20
script/server:3</P>
<P>trying to connect to <SPAN class=3Dcaps>SQL 2000</SPAN> using rails =
1.2.3</P>
<P><STRONG>Is this information summarized somewhere ?</STRONG></P>
<P>As of July 13, 2007, what it the actual state of using Rails 1.2 with =
<SPAN=20
class=3Dcaps>SQL </SPAN>Server 2005?</P>
<P>There=E2=80=99s a lot of info above, but it=E2=80=99s confusing.</P>
<P><STRONG>DBI and <SPAN class=3Dcaps>ODBC</SPAN> and Sql Native=20
Client</STRONG></P>
<P>I don=E2=80=99t know about Rails specific issues since I=E2=80=99m =
just using <SPAN=20
class=3Dcaps>DBI</SPAN> directly. The easiest way I=E2=80=99ve found to =
connect is using=20
<SPAN class=3Dcaps>ODBC</SPAN>. I just use windows Admin Tools <SPAN=20
class=3Dcaps>ODBC </SPAN>Connections to configure the <SPAN =
class=3Dcaps>ODBC</SPAN>=20
connection. I tell it to use <SPAN class=3Dcaps>SQL </SPAN>Native Client =
since I=20
have it. Then it is easy to specify the connection as =
=E2=80=9CDBI:ODBC:connection_name=E2=80=9D=20
where connection_name is the name I specified in the windows <SPAN=20
class=3Dcaps>ODBC</SPAN> configuration.</P>
<P><STRONG>SQL Server 2005: Transactions Inside Migrations Do Not Work =
Correctly=20
When Using <SPAN class=3Dcaps>SQL </SPAN>Native Client =
(SQLNCLI)</STRONG></P>
<P>Using Rails 1.2.3 connecting to <SPAN class=3Dcaps>SQL </SPAN>Server =
2005, I=20
noticed that transactions inside of migrations were not rolling back =
correctly=20
when an error occurred. I switched the provider to <SPAN=20
class=3Dcaps>SQLOLEDB</SPAN> and transactions began to work. I followed =
the steps=20
described above to install the <SPAN class=3Dcaps>ADO</SPAN> stuff and =
modify the=20
<SPAN class=3Dcaps>SQL </SPAN>Server adapter. The following is an =
example of the=20
database configuration in my database.yml file.</P><PRE><CODE>
development:
  adapter: sqlserver
  database: &lt;your db&gt;
  username: sa
  password: password
  host: .
  mode: ADO
  provider: SQLOLEDB
</CODE><BR></PRE>
<DIV class=3Dbyline>Updated on January 25, 2008 09:44 by <A=20
href=3D"http://wiki.rubyonrails.org/rails/pages/Anonymous+Coward">Anonymo=
us=20
Coward</A> (88.97.245.57) </DIV>
<DIV class=3Dnavigation><A=20
href=3D"http://wiki.rubyonrails.com/rails/pages/howtoconnecttomicrosoftsq=
lserver/versions/new">Edit</A>=20
| <A class=3Dnavlink=20
href=3D"http://wiki.rubyonrails.org/rails/pages/HowtoConnectToMicrosoftSQ=
LServer/versions/295">Back=20
in time</A> <SMALL>(295 older versions)</SMALL> </DIV>
<DIV id=3Dfooter>
<P><A href=3D"http://instiki.org/">i2</A> on <A=20
href=3D"http://rubyonrails.com/">Rails</A></P></DIV></DIV></DIV></BODY></=
HTML>

------=_NextPart_000_0000_01C8B1E2.971685A0
Content-Type: application/octet-stream
Content-Transfer-Encoding: base64
Content-Location: http://wiki.rubyonrails.com/images/favicon.png?1171099937

iVBORw0KGgoAAAANSUhEUgAAABAAAAAQCAYAAAAf8/9hAAAAWElEQVQ4jWNkgIJVckz/GcgAjJRo
ZmBgYGDEpTnkwR+sGtYosKDwmUjRjA2woAuga0a3ER1gdQGxmgkaQAwYNWBYGIAzLxADwh79YyTb
BWGP/jEyMDAwAABt+xSx5rzG8gAAAABJRU5ErkJggg==

------=_NextPart_000_0000_01C8B1E2.971685A0
Content-Type: application/octet-stream
Content-Transfer-Encoding: quoted-printable
Content-Location: http://wiki.rubyonrails.com/stylesheets/instiki.css?1171099937

BODY {
	PADDING-RIGHT: 0px; PADDING-LEFT: 0px; BACKGROUND: =
url(/images/topbluefade.gif) #fff repeat-x; PADDING-BOTTOM: 0px; MARGIN: =
10px 0px 0px; PADDING-TOP: 0px; TEXT-ALIGN: center
}
BODY {
	COLOR: #333; FONT-FAMILY: verdana, "Lucida Grande", arial, helvetica, =
sans-serif
}
P {
	COLOR: #333; FONT-FAMILY: verdana, "Lucida Grande", arial, helvetica, =
sans-serif
}
OL {
	COLOR: #333; FONT-FAMILY: verdana, "Lucida Grande", arial, helvetica, =
sans-serif
}
UL {
	COLOR: #333; FONT-FAMILY: verdana, "Lucida Grande", arial, helvetica, =
sans-serif
}
TD {
	COLOR: #333; FONT-FAMILY: verdana, "Lucida Grande", arial, helvetica, =
sans-serif
}
#Container {
	FLOAT: none; MARGIN: 0px auto; TEXT-ALIGN: center
}
#Content {
	PADDING-RIGHT: 5px; PADDING-LEFT: 5px; FLOAT: left; PADDING-BOTTOM: =
5px; MARGIN: 0px; BORDER-TOP-STYLE: none; PADDING-TOP: 5px; TEXT-ALIGN: =
left
}
DIV#Header {
	MARGIN: 15px 0px 10px; TEXT-ALIGN: center
}
DIV#Header IMG {
	FLOAT: right; MARGIN: 0px; VERTICAL-ALIGN: middle; BORDER-TOP-STYLE: =
none; BORDER-RIGHT-STYLE: none; BORDER-LEFT-STYLE: none; =
BORDER-BOTTOM-STYLE: none
}
DIV#Header DIV#Nav IMG {
	FLOAT: none; MARGIN-RIGHT: 5px
}
DIV#Header DIV#Nav {
	FONT-SIZE: 10px; COLOR: #999
}
DIV#Header DIV#Nav A:link {
	FONT-WEIGHT: bold; FONT-SIZE: 11px; COLOR: #000
}
DIV#Header DIV#Nav A:visited {
	FONT-WEIGHT: bold; FONT-SIZE: 11px; COLOR: #000
}
DIV#Header DIV#Nav A:hover {
	BACKGROUND: #333; COLOR: #fff
}
DIV#Header DIV.clearall {
	CLEAR: both; PADDING-RIGHT: 0px; PADDING-LEFT: 0px; FONT-SIZE: 2px; =
PADDING-BOTTOM: 0px; MARGIN: 0px 0px 20px; PADDING-TOP: 0px
}
A {
	COLOR: #369
}
A:visited {
	COLOR: #369
}
A:hover {
	BACKGROUND-COLOR: #f4dda6
}
.newWikiWord {
	BACKGROUND-COLOR: #eee
}
.newWikiWord A:hover {
	BACKGROUND-COLOR: white
}
H1 {
	COLOR: #333; FONT-FAMILY: georgia, verdana, sans-serif
}
H2 {
	COLOR: #333; FONT-FAMILY: georgia, verdana, sans-serif
}
H3 {
	COLOR: #333; FONT-FAMILY: georgia, verdana, sans-serif
}
H1 {
	FONT-SIZE: 28px
}
H2 {
	FONT-SIZE: 19px
}
H3 {
	FONT-SIZE: 16px
}
H1#pageName {
	PADDING-RIGHT: 0px; PADDING-LEFT: 0px; PADDING-BOTTOM: 0px; MARGIN: 5px =
0px 0px; COLOR: #000; LINE-HEIGHT: 28px; PADDING-TOP: 0px; POSITION: =
relative
}
H1#pageName SMALL {
	PADDING-RIGHT: 0px; PADDING-LEFT: 0px; FONT-SIZE: 10px; LEFT: 0px; =
PADDING-BOTTOM: 0px; COLOR: #444; LINE-HEIGHT: 10px; PADDING-TOP: 0px; =
POSITION: absolute; TOP: -15px
}
H1#pageName BR {
	DISPLAY: none
}
#i2Nav {
	MARGIN: 40px 0px 15px
}
* HTML #i2Nav {
	HEIGHT: 112px
}
A.nav {
	COLOR: #000
}
A.nav:link {
	COLOR: #000
}
A.nav:visited {
	COLOR: #000
}
A.nav:hover {
	COLOR: #fff; BACKGROUND-COLOR: #000
}
LI {
	MARGIN-BOTTOM: 7px
}
.navigation {
	MARGIN-TOP: 5px; FONT-SIZE: 12px; COLOR: #999
}
.navigation A:hover {
	COLOR: #fff; BACKGROUND-COLOR: #000
}
.navigation A {
	FONT-WEIGHT: bold; FONT-SIZE: 11px; COLOR: black
}
.navigation SMALL A {
	FONT-WEIGHT: normal; FONT-SIZE: 11px
}
.navOn {
	FONT-WEIGHT: bold; FONT-SIZE: 11px; COLOR: #444; TEXT-DECORATION: none
}
.help {
	FONT-SIZE: 11px; FONT-FAMILY: verdana, arial, helvetica, sans-serif
}
.inputBox {
	PADDING-RIGHT: 5px; PADDING-LEFT: 5px; FONT-SIZE: 11px; MARGIN-BOTTOM: =
20px; PADDING-BOTTOM: 5px; PADDING-TOP: 5px; FONT-FAMILY: verdana, =
arial, helvetica, sans-serif; BACKGROUND-COLOR: #eee
}
BLOCKQUOTE {
	PADDING-RIGHT: 30px; DISPLAY: block; PADDING-LEFT: 30px; FONT-SIZE: =
11px; PADDING-BOTTOM: 0px; MARGIN: 0px 0px 20px; LINE-HEIGHT: 17px; =
PADDING-TOP: 0px; FONT-STYLE: italic
}
PRE {
	PADDING-RIGHT: 10px; PADDING-LEFT: 10px; FONT-SIZE: 11px; =
PADDING-BOTTOM: 10px; OVERFLOW: auto; PADDING-TOP: 10px; =
BACKGROUND-COLOR: #eee
}
OL.setup {
	PADDING-LEFT: 25px; FONT-SIZE: 19px; FONT-FAMILY: georgia, verdana, =
sans-serif
}
OL.setup LI {
	MARGIN-BOTTOM: 20px
}
.byline {
	FONT-SIZE: 10px; MARGIN-BOTTOM: 10px; COLOR: #999; FONT-STYLE: italic
}
.references {
	FONT-SIZE: 10px
}
.diffdel {
	BACKGROUND: pink
}
DEL.diffmod {
	BACKGROUND: pink
}
.diffins {
	BACKGROUND: lightgreen
}
INS.diffmod {
	BACKGROUND: lightgreen
}
#footer {
	PADDING-RIGHT: 0px; PADDING-LEFT: 0px; PADDING-BOTTOM: 0.25em; =
PADDING-TOP: 0.25em; HEIGHT: 14px
}
#footer P {
	FONT-SIZE: 10px; FLOAT: right; MARGIN: 0px; COLOR: gray; FONT-STYLE: =
italic; TEXT-ALIGN: right
}
#error {
	WIDTH: 600px; COLOR: #8b0000; FONT-STYLE: italic
}
#info {
	WIDTH: 600px; COLOR: #006400; FONT-STYLE: italic
}
#TextileHelp TABLE {
	MARGIN-BOTTOM: 0px
}
#TextileHelp TABLE + H3 {
	MARGIN-TOP: 11px
}
#TextileHelp TABLE TD {
	PADDING-RIGHT: 3px; BORDER-TOP: #ccc 1px dotted; PADDING-LEFT: 3px; =
FONT-SIZE: 11px; PADDING-BOTTOM: 3px; VERTICAL-ALIGN: top; PADDING-TOP: =
3px
}
#TextileHelp TABLE TD.arrow {
	PADDING-RIGHT: 5px; PADDING-LEFT: 10px; COLOR: #999
}
#TextileHelp TABLE TD.label {
	PADDING-RIGHT: 15px; FONT-WEIGHT: bold; FONT-SIZE: 10px; COLOR: #000; =
WHITE-SPACE: nowrap
}
#TextileHelp H3 {
	PADDING-RIGHT: 0px; PADDING-LEFT: 0px; FONT-WEIGHT: normal; FONT-SIZE: =
11px; PADDING-BOTTOM: 0px; MARGIN: 0px 0px 5px; PADDING-TOP: 5px
}
#TextileHelp P {
	FONT-SIZE: 10px
}
#editForm {
	FLOAT: left; MARGIN-RIGHT: -255px
}
#version_body {
	WIDTH: 400px; HEIGHT: 500px
}
#MarkupHelp {
	MARGIN-TOP: 5px; FONT-SIZE: 12px; FLOAT: right; WIDTH: 250px
}
.newsList {
	MARGIN-TOP: 20px
}
.newsList P {
	MARGIN-BOTTOM: 30px
}
TD {
=09
}
TABLE {
	BORDER-LEFT-COLOR: black; BORDER-BOTTOM-COLOR: black; BORDER-TOP-STYLE: =
double; BORDER-TOP-COLOR: black; BORDER-RIGHT-STYLE: double; =
BORDER-LEFT-STYLE: double; BORDER-COLLAPSE: collapse; =
BORDER-RIGHT-COLOR: black; BORDER-BOTTOM-STYLE: double
}
.byline {
	PADDING-TOP: 15px
}
.property {
	FONT-SIZE: 10px
}
.c:unknown {
	CLEAR: both; DISPLAY: block; VISIBILITY: hidden; HEIGHT: 0px; content: =
"."
}
.c {
=09
}
* HTML .c {
	HEIGHT: 1%
}
.c {
	DISPLAY: block
}

------=_NextPart_000_0000_01C8B1E2.971685A0
Content-Type: application/octet-stream
Content-Transfer-Encoding: quoted-printable
Content-Location: http://wiki.rubyonrails.com/stylesheets/captcha.css?1171099937

#brain_buster_captcha {
	DISPLAY: block; FONT-SIZE: 12px
}
#brain_buster_captcha INPUT#captcha_answer {
=09
}
.captcha_failed#brain_buster_captcha LABEL {
	PADDING-RIGHT: 1px; PADDING-LEFT: 1px; PADDING-BOTTOM: 1px; =
PADDING-TOP: 1px; BACKGROUND-COLOR: #ffff99
}
#captcha_credit {
	PADDING-RIGHT: 0px; DISPLAY: block; PADDING-LEFT: 0px; FONT-SIZE: 10px; =
PADDING-BOTTOM: 0px; MARGIN: 0px; PADDING-TOP: 0px
}

------=_NextPart_000_0000_01C8B1E2.971685A0--
